How to Add a Windows Domain Login to SQL Server via T-SQL

Using the SQL Server Management Studio (SSMS) GUI to add logins is slow and inefficient. If you need to quickly add a Windows domain account to your SQL Server and grant it full administrative privileges, using T-SQL is the most direct approach.

Here is the exact script to create the domain login, assign it to the sysadmin role, and verify the permissions, along with the command-line steps to test it.

The Script

This script provisions a Windows account in SQL Server and elevates it. Replace DOMAIN\UserName and ServerName with your actual environment variables.

-- Switch to the master database
USE master;
GO

-- Create the domain login (Example: [DOMAIN\UserName])
CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS;
GO

-- Add the login to the sysadmin server role
-- Note: Only run this once. Running it multiple times is unnecessary.
ALTER SERVER ROLE sysadmin
ADD MEMBER [DOMAIN\UserName];
GO

-- Verify the login exists
SELECT name, type_desc
FROM sys.server_principals
WHERE name = 'DOMAIN\UserName';
GO

-- Verify sysadmin membership
SELECT p.name, r.name AS ServerRole
FROM sys.server_role_members rm
JOIN sys.server_principals p
    ON rm.member_principal_id = p.principal_id
JOIN sys.server_principals r
    ON rm.role_principal_id = r.principal_id
WHERE p.name = 'DOMAIN\UserName'
  AND r.name = 'sysadmin';
GO

/* =========================================
   TESTING VIA COMMAND LINE (CMD)
   ========================================= */

-- Test login if you are currently logged into Windows as the target user:
-- sqlcmd -S ServerName -E

-- Test login if you are logged in as a different user:
-- runas /netonly /user:DOMAIN\UserName "sqlcmd -S ServerName -E"

Key Technical Details

  • Security Warning: The sysadmin role grants unrestricted, complete control over the SQL Server instance. Only assign this to required DBAs or critical service accounts.
  • Role Assignment: You only need to run the ALTER SERVER ROLE command once. Do not duplicate it in your scripts.
  • Impersonation Testing: The runas /netonly flag is the fastest way to test the new credentials over the network without having to log out of your current Windows desktop session.